﻿using Dapper;
using isky.Plugin;
using Microsoft.Xrm.Sdk;
using Microsoft.Xrm.Sdk.Query;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace isky.plugin
{
    public class SalesorderapprovalHistoryDal
    {
        /// <summary>
        /// 根据订单查询订单审批历史记录
        /// </summary>
        /// <param name="salesorderid">订单编号</param>
        /// <returns></returns>
        //public IEnumerable<dynamic> GetSalesorderapprovalHistoryInfo(Guid salesorderid)
        //{

        //    var qeQuery = CrmQuery.Select(new ColumnSet(true))
        //                         .From("new_salesorderapprovalhistory")
        //                          .Where("new_salesorderapprovalhistory", "new_salesorder", ConditionOperator.Equal, new object[] { salesorderid })
        //                          .OrderBy(new string[] { "createdon" }, OrderType.Descending)
        //                          .Query;
        //    var result = service.RetrieveMultiple(qeQuery);
        //    if (result != null
        //        && result.Entities != null
        //        && result.Entities.Count > 0)
        //    {
        //        return result;
        //    }
        //    return null;

        //    string cmdText = @"select new_name,new_approvalnode,new_status,new_handler,new_handletime,new_opinion,new_nexthandler from new_salesorderapprovalhistorybase";
        //    SqlBuilder sqlbuilder = new SqlBuilder();
        //    sqlbuilder.Where("new_salesorder=@salesorderid", new { salesorderid = salesorderid });
        //    var sqlTemplate = sqlbuilder.AddTemplate(cmdText);
        //    using (IDbConnection dbconn = ConnectionFactory.CreateConnection(ConnectionFactory.XRMDBName))
        //    {
        //        return dbconn.Query(sqlTemplate.RawSql, sqlTemplate.Parameters);
        //    }
        //}

        /// <summary>
        /// 根据订单查询订单审批历史记录
        /// </summary>
        /// <param name="service"></param>
        /// <param name="salesorderid"></param>
        /// <returns></returns>
        public EntityCollection GetSalesorderapprovalHistoryInfo(IOrganizationService service, Guid salesorderid)
        {
            var qeQuery = CrmQuery.Select(new ColumnSet(true))
                                  .From("new_salesorderapprovalhistory")
                                   .Where("new_salesorderapprovalhistory", "new_salesorder", ConditionOperator.Equal, new object[] { salesorderid })
                                   .OrderBy(new string[] { "createdon" }, OrderType.Descending)
                                   .Query;
            var result = service.RetrieveMultiple(qeQuery);
            if (result != null
                && result.Entities != null
                && result.Entities.Count > 0)
            {
                return result;
            }
            return null;
        }

        /// <summary>
        /// 根据区域查询 审批流程
        /// </summary>
        /// <param name="service"></param>
        /// <param name="territory"></param>
        /// <returns></returns>
        public EntityCollection GetSalesorderapprovalProcessInfo(IOrganizationService service, int territory)
        {
            var qeQuery = CrmQuery.Select(new ColumnSet(true))
                                  .From("new_salesorderapprovalprocess")
                                   .Where("new_salesorderapprovalprocess", "new_territory", ConditionOperator.Equal, new object[] { territory })
                                   .Where("new_salesorderapprovalprocess", "new_enablestatus", ConditionOperator.Equal, new object[] { true })
                                   .Where("new_salesorderapprovalprocess", "statecode", ConditionOperator.Equal, new object[] { 0 })
                                   .Where("new_salesorderapprovalprocess", "statuscode", ConditionOperator.Equal, new object[] { 1 })
                                    .OrderBy(new string[] { "createdon" }, OrderType.Descending)
                                   .Query;
            var result = service.RetrieveMultiple(qeQuery);
            if (result != null
                && result.Entities != null
                && result.Entities.Count > 0)
            {
                return result;
            }
            return null;
        }

        /// <summary>
        /// 根据用户编号查询 安全角色
        /// </summary>
        /// <param name="service"></param>
        /// <param name="systemuserid"></param>
        /// <returns></returns>
        public EntityCollection GetRoleName(IOrganizationService service, Guid systemuserid)
        {
            string fetchXml = @"<fetch mapping='logical' version='1.0'>" +
                           "<entity name='systemuserroles'>" +
                           "  <filter>" +
                            "   <condition attribute='systemuserid' operator='eq' value='"+ systemuserid + "'/>" +
                            " </filter>" +
                            " <link-entity name='role' from='roleid' to='roleid' alias='rol' link-type='inner'>" +
                            "   <attribute name='name'/>" +
                            " </link-entity>" +
                          " </entity>" +
                        "</fetch>";
            EntityCollection roleCollection = service.RetrieveMultiple(new FetchExpression(fetchXml));
            if (roleCollection != null && roleCollection.Entities.Count > 0)
            {
                return roleCollection;
            }
            
            return null;

        }
     

    }
}
